/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import beans.Book;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 */
public class DBAccess {

    private DBConnectionPool conPool;
    private static DBAccess theInstance = null;

    public static DBAccess getInstance() throws ClassNotFoundException {
        if (theInstance == null) {
            theInstance = new DBAccess();
        }
        return theInstance;
    }

    private DBAccess() {
        try {
            conPool = DBConnectionPool.getInstance();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<Book> getBooksbyAuthor(String searchauthor) {
        try {

            ArrayList<Book> books = new ArrayList<Book>();
            Statement stat = conPool.getConnection().createStatement();

            String sqlString = "SELECT books.title, authors.name, price,publishers.name, books.isbn "
                    + "FROM books INNER JOIN booksauthors ON (books.isbn = booksauthors.isbn) "
                    + "INNER JOIN authors ON (authors.authorid = booksauthors.authorid) INNER JOIN publishers ON (books.publisherid = publishers.publisherid) Where upper(authors.name) like upper('%" + searchauthor + "%')";

            ResultSet rs = stat.executeQuery(sqlString);
            while (rs.next()) {
                String title = rs.getString(1);
                String[] split = rs.getString(2).trim().split(",");
                String author = split[0].toUpperCase() + " " + split[1];
                double price = rs.getDouble(3);
                String publisher = rs.getString(4);
                String isbn = rs.getString(5);
                Book b = new Book(title, author, price, publisher, isbn);
                System.out.println(b.toString());
                int index = books.indexOf(b);
                if (index == -1) {
                    books.add(b);
                } else {
                    Book book = books.get(index);
                    book.setAuthor(book.getAuthor() + ", " + author);
                }
            }
            return books;
        } catch (Exception ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Book> getBooksbyName(String search) {
        try {

            ArrayList<Book> books = new ArrayList<Book>();
            Statement stat = conPool.getConnection().createStatement();

            String sqlString = "SELECT books.title, authors.name, price,publishers.name, books.isbn "
                    + "FROM books INNER JOIN booksauthors ON (books.isbn = booksauthors.isbn) "
                    + "INNER JOIN authors ON (authors.authorid = booksauthors.authorid) INNER JOIN publishers ON (books.publisherid = publishers.publisherid) Where upper(books.title) like upper('%" + search + "%')";
            // System.out.println(sqlString);
            ResultSet rs = stat.executeQuery(sqlString);
            while (rs.next()) {
                String title = rs.getString(1);
                String[] split = rs.getString(2).trim().split(",");
                String author = split[0].toUpperCase() + " " + split[1];
                double price = rs.getDouble(3);
                String publisher = rs.getString(4);
                String isbn = rs.getString(5);
                Book b = new Book(title, author, price, publisher, isbn);
                //   System.out.println(b.toString());
                int index = books.indexOf(b);
                if (index == -1) {
                    books.add(b);
                } else {
                    Book book = books.get(index);
                    book.setAuthor(book.getAuthor() + ", " + author);
                }
            }
            return books;
        } catch (Exception ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Book> getBooksbyVerlag(String search) {
        try {

            ArrayList<Book> books = new ArrayList<Book>();
            Statement stat = conPool.getConnection().createStatement();

            String sqlString = "SELECT books.title, authors.name, price,publishers.name, books.isbn "
                    + "FROM books INNER JOIN booksauthors ON (books.isbn = booksauthors.isbn) "
                    + "INNER JOIN authors ON (authors.authorid = booksauthors.authorid) INNER JOIN publishers ON (books.publisherid = publishers.publisherid) Where upper(publishers.name) like upper( '%" + search + "%')";
            ResultSet rs = stat.executeQuery(sqlString);
            while (rs.next()) {
                String title = rs.getString(1);
                String[] split = rs.getString(2).trim().split(",");
                String author = split[0].toUpperCase() + " " + split[1];
                double price = rs.getDouble(3);
                String publisher = rs.getString(4);
                String isbn = rs.getString(5);
                Book b = new Book(title, author, price, publisher, isbn);
                //  System.out.println(b.toString());
                int index = books.indexOf(b);
                if (index == -1) {
                    books.add(b);
                } else {
                    Book book = books.get(index);
                    book.setAuthor(book.getAuthor() + ", " + author);
                }

            }
            return books;
        } catch (Exception ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Book> getBooksbySearch(String value, String searchType, String sortType) {
        try {

            ArrayList<Book> books = new ArrayList<Book>();
            Statement stat = conPool.getConnection().createStatement();
            String sqlsearchtype = "";
            String sortTyString="";
            if (searchType.equals("Author")) {
                sqlsearchtype = "authors.name";
            } else if (searchType.equals("Titel")) {
                sqlsearchtype = "books.title";
            } else if (searchType.equals("Verlag")) {
                sqlsearchtype = "publishers.name";
            } else {
               return null;
            }
            
            
            if (sortType.equals("Author")) {
                sortType = "authors.name asc";
            } else if (sortType.equals("Titel")) {
                sortType = "books.title asc";
            } else if (sortType.equals("Verlag")) {
                sortType = "publishers.name asc";
                
            }else if (sortType.equals("Preis: Aufsteigend")) {
                sortType = "price asc";
                
            }else if (sortType.equals("Preis: Absteigend")) {
                sortType = "price desc";
                
            }
            else {
               return null;
            }
            String sqlString = "SELECT books.title, authors.name, price,publishers.name, books.isbn "
                    + "FROM books INNER JOIN booksauthors ON (books.isbn = booksauthors.isbn) "
                    + "INNER JOIN authors ON (authors.authorid = booksauthors.authorid) INNER JOIN publishers ON (books.publisherid = publishers.publisherid) Where upper(" + sqlsearchtype + ") like upper( '%" + value + "%')"
                    + " Order by "+sortType+"";
            System.out.println(sqlString);
            ResultSet rs = stat.executeQuery(sqlString);
            while (rs.next()) {
                String title = rs.getString(1);
                String[] split = rs.getString(2).trim().split(",");
                String author = split[0].toUpperCase() + " " + split[1];
                double price = rs.getDouble(3);
                String publisher = rs.getString(4);
                String isbn = rs.getString(5);
                Book b = new Book(title, author, price, publisher, isbn);
                //  System.out.println(b.toString());
                int index = books.indexOf(b);
                if (index == -1) {
                    books.add(b);
                } else {
                    Book book = books.get(index);
                    book.setAuthor(book.getAuthor() + ", " + author);
                }

            }
            return books;
        } catch (Exception ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Book> getBooks() throws Exception {
        ArrayList<Book> books = new ArrayList<Book>();
        Statement stat = conPool.getConnection().createStatement();
        String sqlString = "SELECT books.title, authors.name, price,publishers.name, books.isbn "
                + "FROM books INNER JOIN booksauthors ON (books.isbn = booksauthors.isbn) "
                + "INNER JOIN authors ON (authors.authorid = booksauthors.authorid) INNER JOIN publishers ON (books.publisherid = publishers.publisherid)";
        ResultSet rs = stat.executeQuery(sqlString);

        while (rs.next()) {
            String title = rs.getString(1);
            String[] split = rs.getString(2).trim().split(",");
            String author = split[0].toUpperCase() + " " + split[1];
            double price = rs.getDouble(3);
            String publisher = rs.getString(4);
            String isbn = rs.getString(5);
            Book b = new Book(title, author, price, publisher, isbn);
            int index = books.indexOf(b);
            if (index == -1) {
                books.add(b);
            } else {
                Book book = books.get(index);
                book.setAuthor(book.getAuthor() + ", " + author);
            }
        }
        return books;
    }

    public Book getBooksfromISBN(String isbn) throws Exception {


        PreparedStatement stat = conPool.getConnection().prepareStatement("SELECT books.title, authors.name, price,publishers.name, books.isbn "
                + "FROM books INNER JOIN booksauthors ON (books.isbn = booksauthors.isbn) "
                + "INNER JOIN authors ON (authors.authorid = booksauthors.authorid) INNER JOIN publishers ON (books.publisherid = publishers.publisherid) Where books.isbn =?");
        stat.setString(1, isbn);
        ResultSet rs = stat.executeQuery();
        Book b = null;
        while (rs.next()) {
            String title = rs.getString(1);
            String[] split = rs.getString(2).trim().split(",");
            String author = split[0].toUpperCase() + " " + split[1];
            double price = rs.getDouble(3);
            String publisher = rs.getString(4);
            if (b == null) {
                b = new Book(title, author, price, publisher, isbn);
            }

            b.setAuthor(b.getAuthor() + ", " + author);

        }
        return b;

    }

    public ArrayList<Book> getBooksbyISBNs(ArrayList<String> isbns) {
        ArrayList<Book> books = new ArrayList<Book>();
        for (String isbn : isbns) {
            try {
                Book book = this.getBooksfromISBN(isbn);
                if (book != null) {
                    books.add(book);
                }
            } catch (Exception ex) {
                Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        return books;
    }

    public boolean checkISBN(String isbn) {
        try {
            PreparedStatement stat = conPool.getConnection().prepareStatement("Select isbn FROM books Where isbn=?");
            stat.setString(1, isbn);
            ResultSet rs = stat.executeQuery();

            String sisbn = "";
            while (rs.next()) {
                sisbn = rs.getString("isbn");
            }
            if (sisbn.length() > 0) {
                return true;
            }
        } catch (Exception ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public static void main(String[] args) {
        try {
            DBAccess ac = DBAccess.getInstance();
           
          
            try {
                 // System.out.println( ac.getBooksbyVerlag("a", "Author", "desc", "Title").size()+" "+ac.getBooksbyVerlag("a", "Author", "desc", "Title").get(0));
            } catch (Exception ex) {
                Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
